﻿using System;
using System.Data;
using System.Data.SqlClient;
using System.Collections.Specialized;
using System.Web;
using System.Configuration;

namespace Hutech.Authentication.Library.Data
{
    internal static class DBRole
    {

        /// <summary>
        /// Gets the connection string.
        /// </summary>
        /// <returns></returns>
        private static string GetConnectionString()
        {
            return ConfigurationManager.AppSettings["MSSQLConnectionString_CustommerService27032013"];
        }

        internal static bool RoleCreate(
        Guid roleId,
        Guid siteID,
        string roleName)
        {
            SqlParameterHelper sph = new SqlParameterHelper(GetConnectionString(), "root_Roles_Insert", 3);
            sph.DefineSqlParameter("@RoleId", SqlDbType.UniqueIdentifier, ParameterDirection.Input, roleId);
            sph.DefineSqlParameter("@SiteID", SqlDbType.UniqueIdentifier, ParameterDirection.Input, siteID);
            sph.DefineSqlParameter("@RoleName", SqlDbType.NVarChar, 50, ParameterDirection.Input, roleName);
            int rowsAffected = sph.ExecuteNonQuery();
            return (rowsAffected > -1);
        }

        internal static bool Update(Guid roleId, string roleName)
        {
            SqlParameterHelper sph = new SqlParameterHelper(GetConnectionString(), "root_Roles_Update", 2);
            sph.DefineSqlParameter("@RoleId", SqlDbType.UniqueIdentifier, ParameterDirection.Input, roleId);
            sph.DefineSqlParameter("@RoleName", SqlDbType.NVarChar, 50, ParameterDirection.Input, roleName);
            int rowsAffected = sph.ExecuteNonQuery();
            return (rowsAffected > -1);
        }

        internal static bool Delete(Guid roleId)
        {
            SqlParameterHelper sph = new SqlParameterHelper(GetConnectionString(), "root_Roles_Delete", 1);
            sph.DefineSqlParameter("@RoleId", SqlDbType.UniqueIdentifier, ParameterDirection.Input, roleId);
            int rowsAffected = sph.ExecuteNonQuery();
            return (rowsAffected > -1);
        }

        internal static bool DeleteUserRoles(Guid userId)
        {
            SqlParameterHelper sph = new SqlParameterHelper(GetConnectionString(), "root_UserRoles_DeleteUserRoles", 1);
            sph.DefineSqlParameter("@UserId", SqlDbType.UniqueIdentifier, ParameterDirection.Input, userId);
            int rowsAffected = sph.ExecuteNonQuery();
            return (rowsAffected > -1);
        }

        internal static bool Exists(string roleName)
        {
            SqlParameterHelper sph = new SqlParameterHelper(GetConnectionString(), "root_Roles_RoleExists", 1);
            sph.DefineSqlParameter("@RoleName", SqlDbType.NVarChar, 50, ParameterDirection.Input, roleName);
            int count = Convert.ToInt32(sph.ExecuteScalar());
            return (count > 0);
        }

        internal static IDataReader GetById(Guid roleId)
        {
            SqlParameterHelper sph = new SqlParameterHelper(GetConnectionString(), "root_Roles_SelectOne", 1);
            sph.DefineSqlParameter("@RoleId", SqlDbType.UniqueIdentifier, ParameterDirection.Input, roleId);
            return sph.ExecuteReader();
        }

        internal static IDataReader GetByName(string roleName)
        {
            SqlParameterHelper sph = new SqlParameterHelper(GetConnectionString(), "root_Roles_SelectOneByName", 1);
            sph.DefineSqlParameter("@RoleName", SqlDbType.NVarChar, 50, ParameterDirection.Input, roleName);
            return sph.ExecuteReader();
        }

        internal static IDataReader GetSiteRoles()
        {
            SqlParameterHelper sph = new SqlParameterHelper(GetConnectionString(), "root_Roles_SelectAll", 0);
            return sph.ExecuteReader();
        }

        internal static IDataReader GetRolesOfUser(Guid UserID)
        {
            SqlParameterHelper sph = new SqlParameterHelper(GetConnectionString(), "root_UserRoles_CountRoleOfUser", 1);
            sph.DefineSqlParameter("@UserID", SqlDbType.UniqueIdentifier, ParameterDirection.Input, UserID);
            return sph.ExecuteReader();
        }


        internal static IDataReader GetRoleMembers(Guid roleId)
        {
            SqlParameterHelper sph = new SqlParameterHelper(GetConnectionString(), "root_UserRoles_SelectByRoleId", 1);
            sph.DefineSqlParameter("@RoleId", SqlDbType.UniqueIdentifier, ParameterDirection.Input, roleId);
            return sph.ExecuteReader();
        }

        internal static int GetCountOfUsersNotInRole(Guid roleId)
        {
            SqlParameterHelper sph = new SqlParameterHelper(GetConnectionString(), "root_UserRoles_CountNotInRole", 1);
            sph.DefineSqlParameter("@RoleId", SqlDbType.UniqueIdentifier, ParameterDirection.Input, roleId);

            return Convert.ToInt32(sph.ExecuteScalar());
        }

        internal static IDataReader GetUsersNotInRole(Guid roleId)
        {
            SqlParameterHelper sph = new SqlParameterHelper(GetConnectionString(), "root_UserRoles_SelectNotInRole", 1);
            sph.DefineSqlParameter("@RoleId", SqlDbType.UniqueIdentifier, ParameterDirection.Input, roleId);
            return sph.ExecuteReader();
        }

        internal static IDataReader GetRolesUserIsNotIn(
        Guid userId)
        {
            SqlParameterHelper sph = new SqlParameterHelper(GetConnectionString(), "root_Roles_SelectRolesUserIsNotIn", 1);
            sph.DefineSqlParameter("@UserId", SqlDbType.UniqueIdentifier, ParameterDirection.Input, userId);
            return sph.ExecuteReader();
        }

        internal static bool AddUser(Guid roleId, Guid userId)
        {
            SqlParameterHelper sph = new SqlParameterHelper(GetConnectionString(), "root_UserRoles_Insert", 2);
            sph.DefineSqlParameter("@RoleId", SqlDbType.UniqueIdentifier, ParameterDirection.Input, roleId);
            sph.DefineSqlParameter("@UserId", SqlDbType.UniqueIdentifier, ParameterDirection.Input, userId);

            int rowsAffected = sph.ExecuteNonQuery();
            return (rowsAffected > -1);
        }

        internal static bool RemoveUser(Guid roleId, Guid userId)
        {
            SqlParameterHelper sph = new SqlParameterHelper(GetConnectionString(), "root_UserRoles_Delete", 2);
            sph.DefineSqlParameter("@RoleId", SqlDbType.UniqueIdentifier, ParameterDirection.Input, roleId);
            sph.DefineSqlParameter("@UserId", SqlDbType.UniqueIdentifier, ParameterDirection.Input, userId);
            int rowsAffected = sph.ExecuteNonQuery();
            return (rowsAffected > -1);
        }

        internal static IDataReader GetRoleNotInModule(Guid ModuleID)
        {
            SqlParameterHelper sph = new SqlParameterHelper(GetConnectionString(), "root_RolePermission_GetRoleNotInModule", 1);
            sph.DefineSqlParameter("@ModuleID", SqlDbType.UniqueIdentifier, ParameterDirection.Input, ModuleID);
            return sph.ExecuteReader();
        }
    }
}
